package com.song.dao.impl;



import com.song.dao.ExpressDao;

import com.song.exception.expressException.ExpressCodeException;
import com.song.exception.expressException.ExpressNumberException;
import com.song.polo.Express;
import com.song.utils.DateUtil;
import com.song.utils.TemplateUtil;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;


import javax.annotation.Resource;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Repository
public class ExpressDaoImpl implements ExpressDao {
    /**通过录入人手机号与快递状态查询快递的sql*/
    private static final String SELECT_PHONE_STATUS_WRITER_SQL = "select * from  express where writerPhone = ? and status = ?";
    /**通过验证码更新status的值sql*/
    private static final String UPDATE_STATUS = "update express set code = null, outputTime = ?,status = 0 where code = ?";
    /**通过手机号与快递状态查询快递的sql*/
    private static final String SELECT_PHONE_STATUS_SQL = "select * from  express where receiverPhone = ? and status = ?";
    /**
     * 查询控制台数据的sql
     */
    private static final String CONSOLE = "SELECT count(expressId) total,COUNT(TO_DAYS(inputTime)=TO_DAYS(NOW()) or NULL) input,COUNT(TO_DAYS(outputTime)=TO_DAYS(NOW()\n" +
            "\n" +
            ") AND status=0 or null) output,COUNT(status=1 or null) stay from express";
    /**
     * 查询全部快递信息sql
     */
    private static final String SELECTALL_SQL = "SELECT * FROM express";
    /**
     * 通过手机号查询快递信息的sql
     */
    private static final String SELECTBYPHONE_SQL = "SELECT * FROM express WHERE receiverPhone = ?";
    /**
     * 通过快递编号查询快递信息的sql
     */
    private static final String SELECTBYEXPRESSID_SQL = "SELECT * FROM express WHERE expressId = ?";
    /**
     * 通过验证码查询快递信息的sql
     */
    private static final String SELECTBYCODE_SQL = "SELECT * FROM express WHERE code = ?";
    /**
     * 通过快递单号查询快递信息的sql
     */
    private static final String SELECTBYNUMBER_SQL = "SELECT * FROM express WHERE expressNumber = ?";
    /**
     * 新增快递的sql
     */
    private static final String INSERTEXPRESS_SQL = "INSERT INTO express values (null,?,?,?,?,?,?,null,?,?)";
    /**修改快递信息的sql*/
    private static final String UPDATEEXPRESS_SQL = "UPDATE express SET expressNumber=?,company=?,receiverName=?,receiverPhone=?,outputTime =?,status=?,writerPhone=? where expressId=?";
    /**
     * 删除快递的sql
     */
    private static final String DELETEEXPRESS_SQL = "DELETE FROM express WHERE expressId=?";

    private static final String UPDATECODE = "UPDATE express SET code = ? where status = ?";
    @Resource
    private JdbcTemplate template;

    /**
     * 查询快递控制台信息
     *
     * @return 信息的集合
     */
    @Override
    public Map<String, Object> expressConsole() {
        Map<String, Object> map = template.queryForMap(CONSOLE);
        return map;
    }

/**
     * 查询所有的快递信息
     *
     * @param limit     判断是否分页 true 分页
     * @param pageIndex 分页时的起始索引
     * @param pageSize  每次分页的显示数据数
     * @return 快递信息集合
     */

    @Override
    public List<Express> selectExpress(Boolean limit, Integer pageIndex, Integer pageSize) {
        List<Express> query;
        String sql = null;
        if (limit) {
            sql = SELECTALL_SQL + " limit ?,?";
             query = template.query(sql, new Object[]{pageIndex, pageSize}, new RowMapper<Express>() {
                @Override
                public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                    Express express = TemplateUtil.getExpress(resultSet);
                    return express;
                }
             });
        } else {
            sql = SELECTALL_SQL;
            query =template.query(sql, new RowMapper<Express>() {
                @Override
                public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                    Express express = TemplateUtil.getExpress(resultSet);
                    return express;
                }
            });
        }
        return query;


    }


/**
     * 通过收件人手机号查询快速信息
     *
     * @param receiverPhone 收件人手机号
     * @return 收件人的所有快递信息
     */

    @Override
    public List<Express> selectByPhone(String receiverPhone) {
        return template.query(SELECTBYPHONE_SQL, new Object[]{receiverPhone}, new RowMapper<Express>() {
            @Override
            public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                Express express = TemplateUtil.getExpress(resultSet);
                return express;
            }
        });

    }


/**
     * 通过验证码查询快递信息
     *
     * @param code 收件人的验证码
     * @return 快递信息对象
     */

    @Override
    public Express selectByCode(String code) {
        return template.queryForObject(SELECTBYCODE_SQL, new Object[]{code}, new RowMapper<Express>() {
            @Override
            public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                Express express = TemplateUtil.getExpress(resultSet);
                return express;
            }
        });
    }

/**
     * 通过快递编号查询快递信息
     *
     * @param expressId 快递编号
     * @return 查询到的快递信息
     */

    @Override
    public Express selectById(Integer expressId) {
        return template.queryForObject(SELECTBYEXPRESSID_SQL, new Object[]{expressId}, new RowMapper<Express>() {
            @Override
            public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                Express express = TemplateUtil.getExpress(resultSet);
                return express;
            }
        });
    }


/**
     * 通过快递单号查询快递
     *
     * @param expressNumber 快递单号
     * @return 快递信息
     */

    @Override
    public Express selectByNumber(String expressNumber) {

        try {
            return template.queryForObject(SELECTBYNUMBER_SQL, new Object[]{expressNumber}, new RowMapper<Express>() {
                @Override
                public Express mapRow(ResultSet resultSet, int i) throws SQLException {
                    Express express = TemplateUtil.getExpress(resultSet);
                    return express;
                }
            });
        } catch (DataAccessException e) {
            return null;
        }


    }


/**
     * 新增快递
     *
     * @param express 新增的快递信息对象
     */

    @Override
    public void insertExpress(Express express) throws ExpressNumberException, ExpressCodeException {
        try {
            template.update(INSERTEXPRESS_SQL, express.getExpressNumber(), express.getCompany(),
                    express.getReceiverName(), express.getReceiverPhone(), express.getCode(), DateUtil.getTimestamp(), 1, express.getWriterPhone());
        } catch (Exception e) {
            if (e.getMessage().endsWith("key 'express.expressNumber'")){
                ExpressNumberException e2 = new ExpressNumberException(e.getMessage());
                throw e2;
            }else if (e.getMessage().endsWith("for key 'express.code'")){
                ExpressCodeException e3 = new ExpressCodeException(e.getMessage());
                throw e3;
            }else{
                e.printStackTrace();
            }
        }

          /*  List list = new ArrayList<>();
            list.add(express.getExpressNumber());
            list.add(express.getCompany());
            list.add(express.getReceiverName());
            list.add(express.getReceiverPhone());
            list.add(express.getCode());
            list.add(TransformUtil.getTimestamp());
            list.add(1);
            list.add(express.getWriterPhone());

            int update = update(INSERTEXPRESS_SQL, list);*/


    }



/**
     * 修改快速信息
     *
     * @param express 快递单号
     * @return 1成功 0失败
     */

    @Override
    public Integer updateExpress(Express express) throws ExpressNumberException{
        try {
            int update = template.update(UPDATEEXPRESS_SQL, express.getExpressNumber(), express.getCompany(),
                    express.getReceiverName(), express.getReceiverPhone(),
                    DateUtil.getTimestamp(), express.getStatus(), express.getWriterPhone(), express.getExpressId());
            if (update > 0){
                return 1;
            }
        } catch (DataAccessException e) {
            if (e.getMessage().endsWith("key 'express.expressNumber'")){
                ExpressNumberException e2 = new ExpressNumberException(e.getMessage());
                throw e2;
            }else{
                e.printStackTrace();
            }
        }
        return 0;
       /*         List list = new ArrayList<>();
        list.add(express.getExpressNumber());
        list.add(express.getCompany());
        list.add(express.getReceiverName());
        list.add(express.getReceiverPhone());
        list.add(express.getCode());
        list.add(express.getOutputTime());
        list.add(express.getStatus());
        list.add(express.getWriterPhone());
        list.add(express.getExpressId());*/

    }

/**
     * 删除快速信息
     *
     * @param expressId 快递ID
     * @return 1成功 2失败
     */

    @Override
    public Integer deleteExpress(Integer expressId) {
        int update = template.update(DELETEEXPRESS_SQL, expressId);
        if (update > 0) {
            return 1;
        }
       return 0;

    }

/**
     * 通过手机号查询未取快递
     *
     * @param userPhone 手机号
     * @param status    快递状态
     * @return 快递信息集合
     *//*

    @Override
    public List<Express> selectExpress(String userPhone, Integer status) {
        List<Express> expresses = new ArrayList<>();
        List<Object> list = new ArrayList<>();
        list.add(userPhone);
        list.add(status);
        resultSet = select(SELECT_PHONE_STATUS_SQL, list);
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setExpressId(resultSet.getInt("expressId"));
                express.setExpressNumber(resultSet.getString("expressNumber"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiverName(resultSet.getString("receiverName"));
                express.setCode(resultSet.getString("code"));
                express.setInputTime(resultSet.getTimestamp("inputTime"));
                express.setOutputTime(resultSet.getTimestamp("outputTime"));
                express.setStatus(resultSet.getInt("status"));
                express.setWriterPhone(resultSet.getString("writerPhone"));
                expresses.add(express);
            }
            return expresses;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }

    */
/**
     * 通过手机号查询未取快递
     *
     * @param writerPhone 录入人手机号
     * @param status    快递状态
     * @return 快递信息集合
     *//*

    @Override
    public List<Express> selectExpressByWriterPhone(String writerPhone, Integer status) {
        List<Express> expresses = new ArrayList<>();
        List<Object> list = new ArrayList<>();
        list.add(writerPhone);
        list.add(status);
        resultSet = select(SELECT_PHONE_STATUS_WRITER_SQL, list);
        try {
            while (resultSet.next()) {
                Express express = new Express();
                express.setExpressId(resultSet.getInt("expressId"));
                express.setExpressNumber(resultSet.getString("expressNumber"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiverName(resultSet.getString("receiverName"));
                express.setReceiverPhone(resultSet.getString("receiverPhone"));
                express.setCode(resultSet.getString("code"));
                express.setInputTime(resultSet.getTimestamp("inputTime"));
                express.setOutputTime(resultSet.getTimestamp("outputTime"));
                express.setStatus(resultSet.getInt("status"));
                express.setWriterPhone(resultSet.getString("writerPhone"));
                expresses.add(express);
            }
            return expresses;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }

    */
/**
     * 通过code更新快递状态
     *
     * @param code 快递验证码
     * @return true 成功
     *//*

    @Override
    public Boolean updateStatus(String code) {
        List<Object> list = new ArrayList<>();
        list.add(TransformUtil.getTimestamp());
        list.add(code);
        try {
            int update = update(UPDATE_STATUS, list);
            if (update > 0) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }
*/



}
